﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using System.Data.SqlClient;

namespace PlayGround.WPF
{
    class CollectPerformanceTraceData
    {
        HealthCheckDataContext dc;

        public void CollectData()
        {

            dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            var time = (from a in dc.Times

                        select a.Time1).Max();
            var server = from a in dc.PerfServers
                         select a;
            Time perfTime = new Time();
        
                perfTime.Time1 = DateTime.Now;

            perfTime.TraceID = 1;
            dc.Times.InsertOnSubmit(perfTime);
            dc.SubmitChanges();
            foreach (var item in server)
            {
                new WMIQueries().GetDisk(item, perfTime, dc);
                new WMIQueries().GetCPU(item, perfTime, dc);
                collectSQLInstanceData(perfTime, item);
            }


        }

        private void collectSQLInstanceData(Time time, PerfServer server)
        {
            string connections = @"SELECT  COUNT(dbid) as TotalConnections FROM  sys.sysprocesses  WHERE   dbid > 0";
            string ple = @"SELECT [cntr_value] FROM sys.dm_os_performance_counters  WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'";



            var instances = from i in dc.Instances
                            where i.PerfServer == server
                            select i;
            foreach (var instance in instances)
            {

                SQLServerInstance sqlServerInstance = new SQLServerInstance();
                sqlServerInstance.InstanceName = instance.Name;
                sqlServerInstance.Port = instance.Port;
                sqlServerInstance.Authentication = instance.Authentication;
                sqlServerInstance.Login = instance.Login;
                sqlServerInstance.Password = instance.Password;
                using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(ple, cn);
                    SqlDataReader rd = cmd.ExecuteReader();

                    InstanceData data = new InstanceData();

                    while (rd.Read())
                    {
                        data.pageLifeExpectancy = Convert.ToInt32(rd.GetInt64(0));
                        
                    }
                    rd.Close();
                    cmd = new SqlCommand(connections, cn);
                    rd = cmd.ExecuteReader();

                    while (rd.Read())
                    {
                        data.connections = Convert.ToInt32(rd.GetInt32(0));

                    }
                    rd.Close();




                    data.Instance = instance;
                    data.Time = time;
                    dc.InstanceDatas.InsertOnSubmit(data);
                }

                dc.SubmitChanges();
                // execute sql statement here on the connection you created
            }

        }
    }



}

